 package fact.view.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.annotation.Resource;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.ViewScoped;
import javax.sql.DataSource;

import fact.view.DTO.RespMakeMarketDTO;
import fact.view.DTO.ResponsibilityDTO;
import fact.view.DTO.UserDTO;


@ManagedBean(name = "responsibilityModelBean")
//@RequestScoped
@ViewScoped
public class ResponsibilityModelBean {
	
	@Resource(name="jdbc/factentry")
	private DataSource ds;
	
	public ArrayList<ResponsibilityDTO> getResponsiblityList() throws SQLException
	{
		ArrayList<ResponsibilityDTO> responsiblityList=new ArrayList<ResponsibilityDTO>();
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		PreparedStatement ps = con.prepareStatement("Select res.MAKE_ID,res.MARKET_ID,makes.MAKE_NAME,market.MARKET_NAME,res.USER_ID,muser.USERNAME,res.ROLE_ID,rol.ROLE_NAME  " +
				"from RESPONSIBILITIES res,MST_MAKES makes,MST_MARKETS market,MST_USER muser,MST_ROLE rol " +
				"Where res.MAKE_ID=makes.MAKE_ID " +
				"And   res.MARKET_ID=market.MARKET_ID " +
				"And   res.USER_ID=muser.USERID " +
				"And   res.ROLE_ID=rol.ROLE_ID " +
				"Order by userid,make_id,market_id ");
		
		ResultSet result =  ps.executeQuery();
		
		ResponsibilityDTO r=new ResponsibilityDTO();
		RespMakeMarketDTO rmm=new RespMakeMarketDTO();
		String preUserId="";
		String preRoleId="";
		int counter=0;
		boolean flag=false;
				while(result.next())
				{
					if(!preRoleId.equalsIgnoreCase(result.getString("ROLE_ID")) || !preUserId.equalsIgnoreCase(result.getString("USER_ID")))
					{
						if(counter!=0)
						{
							responsiblityList.add(r);
							r=new ResponsibilityDTO();
							rmm=new RespMakeMarketDTO();
							
							r.setRoleId(result.getInt("ROLE_ID"));
							r.setUserId(result.getString("USER_ID"));							
							r.setRoleName(result.getString("ROLE_NAME"));
							r.setUserName(result.getString("USERNAME"));
							
							rmm.setMakeId(result.getInt("MAKE_ID"));
							rmm.setMarketId(result.getInt("MARKET_ID"));
							rmm.setMakeName(result.getString("MAKE_NAME"));
							rmm.setMarketName(result.getString("MARKET_NAME"));
							
							if(r.getMakeMarketList()==null)
								r.setMakeMarketList(new ArrayList<RespMakeMarketDTO>());
							
							r.getMakeMarketList().add(rmm);
							responsiblityList.add(r);
							flag=false;
							
						}
						else
						{
							r=new ResponsibilityDTO();
							rmm=new RespMakeMarketDTO();
							
							r.setRoleId(result.getInt("ROLE_ID"));
							r.setUserId(result.getString("USER_ID"));
							r.setRoleName(result.getString("ROLE_NAME"));
							r.setUserName(result.getString("USERNAME"));
							
							rmm.setMakeId(result.getInt("MAKE_ID"));
							rmm.setMarketId(result.getInt("MARKET_ID"));
							rmm.setMakeName(result.getString("MAKE_NAME"));
							rmm.setMarketName(result.getString("MARKET_NAME"));
							
							if(r.getMakeMarketList()==null)
								r.setMakeMarketList(new ArrayList<RespMakeMarketDTO>());
							
							r.getMakeMarketList().add(rmm);
							

							
						}
					}
					else
					{
						rmm=new RespMakeMarketDTO();
						rmm.setMakeId(result.getInt("MAKE_ID"));
						rmm.setMarketId(result.getInt("MARKET_ID"));
						rmm.setMakeName(result.getString("MAKE_NAME"));
						rmm.setMarketName(result.getString("MARKET_NAME"));
						
						if(r.getMakeMarketList()==null)
							r.setMakeMarketList(new ArrayList<RespMakeMarketDTO>());
						
						r.getMakeMarketList().add(rmm);
						flag=true;
					}
					preRoleId=result.getString("ROLE_ID");
					preUserId=result.getString("USER_ID");
					
					counter++;
					
				}
		
//				if(flag==true)
//				{
//					r.getMakeMarketList().add(rmm);
//					//responsiblityList.add(r);
//					
//				}
		return responsiblityList;
	}
	
	

	public boolean isResponsibilityOk(String userId,int roleId) throws SQLException
	{
		int total=0;
		
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		PreparedStatement ps = con.prepareStatement("Select count(*) total from Mst_user Where User_Type=? and userid=?");  
		ps.setInt(1, roleId);
		ps.setString(2, userId);

		ResultSet result =  ps.executeQuery();
		if(result.next())
			total=result.getInt("total");
		
		if(total==1)
			return true;
		else
			return false;
		
	}
	public boolean isResponsiblitiyExist(String userId,int roleId,int makeId,int marketId) throws SQLException
	{
		int total=0;
		
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		PreparedStatement ps = con.prepareStatement("Select count(*) total from RESPONSIBILITIES Where User_Id=? and Role_Id=? and Make_Id=? and Market_Id=?");  
		ps.setString(1, userId);
		ps.setInt(2, roleId);
		ps.setInt(3, makeId);
		ps.setInt(4, marketId);

		ResultSet result =  ps.executeQuery();
		if(result.next())
			total=result.getInt("total");
		
		if(total>=1)
			return true;
		else
			return false;
		
	}
	
	public boolean addResponsibility(UserDTO user,String[] assignedMakeList,String[] assignedMarketList) throws SQLException
	{
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		PreparedStatement ps = con.prepareStatement("Delete RESPONSIBILITIES Where USER_ID=?");  
		ps.setString(1, user.getUserId());
		int result =  ps.executeUpdate();
		
		for(int i=0;i<assignedMakeList.length;i++)
		{
			for(int j=0;j<assignedMarketList.length;j++)
			{
				ps = con.prepareStatement("Insert into RESPONSIBILITIES(MAKE_ID,MARKET_ID,ROLE_ID,USER_ID) VALUES(?,?,?,?)");  
				ps.setString(1, assignedMakeList[i]);
				ps.setString(2, assignedMarketList[j]);
				ps.setInt(3, user.getUserRoleId());
				ps.setString(4, user.getUserId());
				
				result =  ps.executeUpdate();
			}
		}
		
		if(result==1)
			return true;
		else
			return false;
		
	}

	public boolean deleteResponsiblity(ResponsibilityDTO resp) throws SQLException
	{
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		PreparedStatement ps = con.prepareStatement("Delete RESPONSIBILITIES Where MAKE_ID=? and MARKET_ID=? and ROLE_ID=? and USER_ID=?");  
		ps.setInt(1, resp.getMakeId());
		ps.setInt(2, resp.getMarketId());
		ps.setInt(3, resp.getRoleId());
		ps.setString(4, resp.getUserId());
		int result =  ps.executeUpdate();
		
		if(result==1)
			return true;
		else
			return false;
		
	}

	

	/*
	public SegmentDTO getSegment(String segmentName) throws SQLException
	{
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		PreparedStatement ps = con.prepareStatement("Select * from MST_SEGMENTS Where upper(SEGMENT_NAME)=upper(?)");  
		ps.setString(1, segmentName);
		ResultSet result =  ps.executeQuery();
		SegmentDTO s=null;
				if(result.next())
				{
					s=new SegmentDTO();
					s.setSegmentId(result.getInt("SEGMENT_ID"));
					s.setSegmentName(result.getString("SEGMENT_NAME"));
					s.setPosition(result.getInt("POSITION"));
					
				}
		
		return s;
	}
	
	public SegmentDTO getSegment(int segmentId) throws SQLException
	{
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		PreparedStatement ps = con.prepareStatement("Select * from MST_SEGMENTS Where SEGMENT_ID=?");  
		ps.setInt(1, segmentId);
		ResultSet result =  ps.executeQuery();
		SegmentDTO s=null;
				if(result.next())
				{
					s=new SegmentDTO();
					s.setSegmentId(result.getInt("SEGMENT_ID"));
					s.setSegmentName(result.getString("SEGMENT_NAME"));
					s.setPosition(result.getInt("POSITION"));				
				}
		
		
		return s;
	}
	
	
	public boolean addSegment(String segmentName,int position) throws SQLException
	{
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		PreparedStatement ps = con.prepareStatement("Insert into MST_SEGMENTS Values(SQN_SEGMENT.NEXTVAL,?,?)");  
		ps.setString(1, segmentName);
		ps.setInt(2, position);
		int result =  ps.executeUpdate();
		
		if(result==1)
			return true;
		else
			return false;
		
	}
	
	public boolean deleteSegment(int segmentId) throws SQLException
	{
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		PreparedStatement ps = con.prepareStatement("Delete MST_SEGMENTS Where SEGMENT_ID=?");  
		ps.setInt(1, segmentId);
		int result =  ps.executeUpdate();
		
		if(result==1)
			return true;
		else
			return false;
		
	}
	
*/}